--select * FROM fn_load_view_sales_orders_with_enduserpartnumbers ( 0, 0, 1, 'All', 0 )
ALTER function fn_load_view_sales_orders_with_enduserpartnumbers(@product_id_I int, 
@customer_id_I int, @is_sample int, @stat_V varchar(50), @endcustomer varchar(65) )

returns  @fn_result Table(SalesOrder_ID int,SalesOrder_No varchar(50),Customer_ID int,Customer_Name varchar(65),
			 LineItemID int,LineItem_No int,Product_ID int,Part_Number varchar(50),mrkt_Part_Number VARCHAR(50),Sales_Hold varchar(3),
			Marketing_Hold varchar(3),Operations_Hold varchar(3),Finance_Hold varchar(3),
			Req_Date varchar(50),Booking_Date varchar(50),Schedule_Date varchar(50),
			Revised_Date varchar(50),Req_Qty int,Shipped_Qty int,Status varchar(50),
			FinalStatus varchar(40),ShipDate varchar(50),COOL_Description varchar(100),
			Customer_PartNumber varchar(50), Customer_PO_No varchar(50),End_Customer VARCHAR(65),End_User varchar(65), Shipment_No int, Order_Type varchar(20))
as
begin
  	-- Table declaration
	declare @results Table(SalesOrder_ID int, SalesOrder_No varchar(50), Customer_ID int, 
	Customer_Name varchar(100), LineItemID int, LineItem_No int,  Product_ID int, Part_Number varchar(50),mrkt_Part_Number VARCHAR(50),
	Sales_Hold varchar(10), Marketing_Hold varchar(10), 
	Operations_Hold varchar(10), Finance_Hold varchar(10), Req_Date varchar(50), 
	Booking_Date varchar(50), Schedule_Date varchar(50), 	Revised_Date varchar(50),  Req_Qty int, 
	Shipped_Qty int, Status varchar(40), FinalStatus varchar(40), 
	ShipDate varchar(50), COOL_Description varchar(50),Customer_PartNumber varchar(50), Customer_PO_No varchar(50),End_Customer VARCHAR(65),End_User varchar(50), Shipment_No int, Order_Type varchar(20) )
	
	declare @so_id_I int, @li_id_I int, @req_qty_I int, @qty_ship_I int, @li_no_I int, @cust_id_I int, @prod_id_I int
	declare	@op_hold_V varchar(10), @mk_hold_V varchar(10), @sa_hold_V varchar(10),  @fi_hold_V varchar(10)
	declare @so_no_V varchar(50), @li_status_V varchar(40), @part_no_V varchar(50), @cust_name_V varchar(100)
	declare @desc_V varchar(50), @temp_cslit_status  varchar(20)
	declare @so_booking_date datetime, @req_dock_date_DT datetime, @sche_date_DT datetime, @ship_date_DT datetime
	declare @rev_ship_date_DT datetime
	declare @customer_partnumber varchar(50)
	declare @customer_po_no varchar(50)
	declare @so_end_user varchar(65),@so_end_customer varchar(65), @mrkt_Part_Number varchar(50)
	declare @odm_id_I int
	declare @shipment_no int
	declare @order_type varchar(20)
	declare @db_is_sample int

	declare @end_user_id int
	set @op_hold_V =''
	set @mk_hold_V =''
	set @sa_hold_V =''
	set @fi_hold_V =''	
	declare @final_status_V varchar(20)

	if(@stat_V <> 'CANCELLED')
	begin
	DECLARE ItemCursor CURSOR FOR
		SELECT a.LineItem_ID, COOL_Status, COOL_Qty, COOL_Line_Item_No, a.Product_ID, COOL_Requested_Date,
			COOL_Schedule_Date, COOL_Revised_Date, COOL_Description, COOL_CUSTOMER_PARTNUMBER,
                        --so.so_enduser,
			CASE WHEN ( b.SO_ODM_ID IS NULL OR b.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = b.SO_ODM_ID )
			END AS EndCustomer,
			CASE WHEN ( b.SO_ENDCUSTOMER_ID IS NULL OR b.SO_ENDCUSTOMER_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = b.SO_ENDCUSTOMER_ID )
			END AS EndUser,
                        SalesOrder_No, b.Customer_ID, SO_Booking_Date, SO_Customer_PO_Number, Part_Number, P_MARKETING_PART_NUMBER,d.C_Name, a.SalesOrder_ID, b.SO_ODM_ID, COOL_Shipment_No, b.SO_Is_Sample 
			FROM customer_open_order_lineitem a,Sales_Order b, Product c, Customer d WITH (NOLOCK)
			WHERE a.SalesOrder_id = b.SalesOrder_Id AND b.SO_Is_Sample = @is_sample
			  and c.Product_ID = a.Product_ID
			  and d.Customer_ID = b.Customer_ID --and b.so_enduser = @enduser
	OPEN  ItemCursor
	FETCH NEXT FROM ItemCursor INTO @li_id_I, @li_status_V, @req_qty_I, @li_no_I, @prod_id_I,
			 @req_dock_date_DT, @sche_date_DT, @rev_ship_date_DT, @desc_V, @customer_partnumber, @so_end_customer,@so_end_user,
 			 @so_no_V, @cust_id_I, @so_booking_date, @customer_po_no, @part_no_V, @mrkt_part_number,@cust_name_V,@so_id_I,@odm_id_I, @shipment_no, @db_is_sample
	while @@fetch_status = 0
	begin	  
		-- Get the Hold Values, only if status is HOLD and PENDING	
		if @li_status_V = 'HOLD' or @li_status_V = 'PENDING' 		
		begin
			select @op_hold_V = Hold_Reason_Code from sales_hold_table where LineItem_ID = @li_id_I 
				and Hold_By_Group = 'OPERATIONS'
			if @op_hold_V = '0' set @op_hold_V = 'X'
			select @mk_hold_V = Hold_Reason_Code from sales_hold_table where
				LineItem_ID = @li_id_I and Hold_By_Group = 'MARKETING'
			if @mk_hold_V = '0' set @mk_hold_V = 'X'
			select @sa_hold_V = Hold_Reason_Code from sales_hold_table where
				 LineItem_ID = @li_id_I and Hold_By_Group = 'SALES'
			if @sa_hold_V = '0' set @sa_hold_V = 'X'
			select @fi_hold_V = Hold_Reason_Code from sales_hold_table where
				 LineItem_ID = @li_id_I and Hold_By_Group = 'FINANCE'
			if @fi_hold_V = '0' set @fi_hold_V = 'X'
			set @qty_ship_I = 0;
			SELECT  @qty_ship_I = COALESCE (SUM(CSLIT_QTY_SHIPPED), null), @temp_cslit_status = CSLIT_STATUS 
				FROM   customer_shipping_line_item_table WITH (NOLOCK)where LineItem_ID = @li_id_I 
				and CSLIT_STATUS <> 'CANCELLED'
				GROUP BY LineItem_ID,CSLIT_STATUS
		end
	 	else if @li_status_V = 'APPROVED'		
	 	begin
			set @temp_cslit_status = '';
			set @qty_ship_I = 0;
			SELECT  @qty_ship_I = COALESCE (SUM(CSLIT_QTY_SHIPPED), null), @temp_cslit_status = CSLIT_STATUS 
				FROM   customer_shipping_line_item_table WITH (NOLOCK)where LineItem_ID = @li_id_I 
				and CSLIT_STATUS <> 'CANCELLED'
				GROUP BY LineItem_ID,CSLIT_STATUS
			if @qty_ship_I is not null and @temp_cslit_status = 'SHIPPED'
			begin
				if  (@req_qty_I-@qty_ship_I ) > 0 
				begin
					set @li_status_V = 'PARTIALLY SHIPPED'
				end
				else if (@req_qty_I-@qty_ship_I ) = 0 
				begin
					set @li_status_V = 'SHIPPED'
				end
			end
			if @temp_cslit_status = 'ISSUED'
			begin
				set @li_status_V = 'PS_ISSUED'
				set @qty_ship_I = 0
			end
		  end
 		else if @li_status_V = 'SHIPPED'
		begin
			SELECT @qty_ship_I = SUM(CSLIT_Qty_Shipped) FROM customer_shipping_line_item_table WITH(NOLOCK)
				WHERE LineItem_ID = @li_id_I and CSLIT_Status = 'SHIPPED'
		end
		
		set @final_status_V = ''
		if @li_status_V = 'HOLD' or @li_status_V = 'APPROVED' or @li_status_V = 'PARTIALLY SHIPPED' or @li_status_V = 'PS_ISSUED' 
		begin
			 	
				set @final_status_V = 'BACKLOG'
		end
		else
		begin
				set @final_status_V = 'SHIPPED'
		end
		
		if @qty_ship_I is null set @qty_ship_I = 0
		if ( @db_is_sample = 2 )
		begin
			set @order_type = 'FCST-Customer'
		end
		else
		begin
			set @order_type = 'Standard'
		end
		
		insert into @results values(@so_id_I, @so_no_V, @cust_id_I, 
			@cust_name_V, @li_id_I, @li_no_I, @prod_id_I, @part_no_V,@mrkt_Part_Number,  
			@sa_hold_V, @mk_hold_V, @op_hold_V, @fi_hold_V, 
			convert(varchar, @req_dock_date_DT,101), convert(varchar, @so_booking_date, 101),
			convert(varchar, @sche_date_DT, 101), convert(varchar, @rev_ship_date_DT, 101),
			@req_qty_I, @qty_ship_I, @li_status_V, @final_status_V, null,  @desc_V, @customer_partnumber, @customer_po_no,@so_end_customer,@so_end_user, @shipment_no, @order_type)
		set @op_hold_V =null
		set @mk_hold_V =null
		set @sa_hold_V =null
		set @fi_hold_V =null
		set @li_id_I = null
		set @li_status_V = null
		set @req_qty_I = null
		set @li_no_I = null
		set @prod_id_I = null 
		set @req_dock_date_DT = null
		set @sche_date_DT = null
		set @rev_ship_date_DT = null
		set @desc_V = null
		set @qty_ship_I = null
		set @customer_partnumber = null
		set @customer_po_no = null
		set @odm_id_I = null
		set @order_type = null
		set @db_is_sample = null
		set @so_end_customer = null
		set @mrkt_Part_Number = null

		fetch next from ItemCursor into @li_id_I, @li_status_V, @req_qty_I, @li_no_I, @prod_id_I,
			 @req_dock_date_DT, @sche_date_DT, @rev_ship_date_DT, @desc_V, @customer_partnumber,@so_end_customer,@so_end_user,
 			 @so_no_V, @cust_id_I, @so_booking_date, @customer_po_no, @part_no_V,@mrkt_Part_Number, @cust_name_V,@so_id_I,@odm_id_I, @shipment_no, @db_is_sample
		
       	end
	close ItemCursor
	deallocate ItemCursor
	end	
	if(@stat_V = 'All' or @stat_V = 'CANCELLED')
	begin
		declare COItemCursor cursor for 
			SELECT a.LineItem_ID, CCOL_Qty, CCOL_Line_Item_No, a.Product_ID, CCOL_Requested_Date,
			CCOL_Schedule_Date, CCOL_Revised_Date, CCOL_CUSTOMER_PARTNUMBER,
                        --SO_EndUser, 
			CASE WHEN ( b.SO_ODM_ID IS NULL OR b.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = b.SO_ODM_ID )
			END AS EndCustomer,
			CASE WHEN ( b.SO_ENDCUSTOMER_ID IS NULL OR b.SO_ENDCUSTOMER_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = b.SO_ENDCUSTOMER_ID )
			END AS EndUser,
                        SalesOrder_No, b.Customer_ID, SO_Booking_Date, SO_Customer_PO_Number, Part_Number, P_MARKETING_PART_NUMBER,C_Name, a.SalesOrder_ID, b.SO_ODM_Id, b.SO_Is_Sample 
			FROM customer_cancel_order_lineitem a,Sales_Order b, Product c, Customer d  WITH (NOLOCK)
			WHERE a.SalesOrder_id = b.SalesOrder_Id AND b.SO_Is_Sample = @is_sample
			  and c.Product_ID = a.Product_ID
			  and d.Customer_ID = b.Customer_ID 
		
	open COItemCursor
	fetch next from COItemCursor into @li_id_I, @req_qty_I, @li_no_I, @prod_id_I, @req_dock_date_DT, 
			@sche_date_DT, @rev_ship_date_DT,@customer_partnumber,@so_end_customer,@so_end_user,@so_no_V, @cust_id_I, @so_booking_date,
			@customer_po_no, @part_no_V, @mrkt_Part_Number, @cust_name_V,@so_id_I,@odm_id_I, @db_is_sample 
	while @@fetch_status = 0
	begin
		if ( @db_is_sample = 2 )
		begin
			set @order_type = 'FCST-Customer'
		end
		else
		begin
			set @order_type = 'Standard'
		end
		insert into @results(SalesOrder_No, LineItemID, Status, Req_Qty, FinalStatus, Booking_Date, Req_Date,
			 Schedule_Date, Revised_Date, LineItem_No, SalesOrder_ID, Customer_ID, Customer_Name,
			 Product_ID, Part_Number, mrkt_Part_Number, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold,
			 Customer_PartNumber, Customer_PO_No,End_Customer,End_User, Shipment_No, Order_Type ) values(@so_no_V, @li_id_I, 'CANCELLED', @req_qty_I, 
			'CANCELLED', convert(varchar, @so_booking_date, 101), convert(varchar, @req_dock_date_DT, 101),
			convert(varchar, @sche_date_DT, 101), convert(varchar, @rev_ship_date_DT,101), @li_no_I, 
			@so_id_I, @cust_id_I, @cust_name_V, @prod_id_I, @part_no_V,@mrkt_Part_Number, '','','','',@customer_partnumber,
			 @customer_po_no,@so_end_customer,@so_end_user, @shipment_no, @order_type)					

		set @customer_partnumber = null

		fetch next from COItemCursor into @li_id_I, @req_qty_I, @li_no_I, @prod_id_I, @req_dock_date_DT, 
			@sche_date_DT, @rev_ship_date_DT,@customer_partnumber,@so_end_customer,@so_end_user,@so_no_V, @cust_id_I, @so_booking_date,
			@customer_po_no, @part_no_V, @mrkt_Part_Number,@cust_name_V,@so_id_I,@odm_id_I, @db_is_sample  

	end
	close COItemCursor
	deallocate COItemCursor
	end	
	if(@product_id_I = 0 and @customer_id_I = 0 and @endcustomer = '0')
	begin
		if @stat_V = 'All' insert @fn_result  select * from @results
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
		else if @stat_V = 'HOLD' insert @fn_result  select * from @results where Status = 'HOLD'
		else insert @fn_result  select * from @results where FinalStatus = @stat_V 
	end
	if(@product_id_I <> 0 and @customer_id_I = 0 and @endcustomer = '0')
	begin
		if @stat_V = 'All'
			 insert @fn_result  select * from @results where Product_ID = @product_id_I
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
				and Product_ID = @product_id_I
		else if @stat_V = 'HOLD' insert @fn_result
			  select * from @results where Status = 'HOLD' and  Product_ID = @product_id_I
		else insert @fn_result
			  select * from @results where FinalStatus = @stat_V and Product_ID = @product_id_I
	end
	if(@product_id_I = 0 and @customer_id_I <> 0  and @endcustomer = '0')
	begin
		if @stat_V = 'All'
			 insert @fn_result  select * from @results where Customer_ID = @customer_id_I
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
				and Customer_ID = @customer_id_I
		else if @stat_V = 'HOLD' insert @fn_result
			  select * from @results where Status = 'HOLD' and  Customer_ID = @customer_id_I
		else insert @fn_result
			  select * from @results where FinalStatus = @stat_V and Customer_ID = @customer_id_I
	end
	if(@product_id_I = 0 and @customer_id_I = 0  and @endcustomer <> '0')
	begin
		if @stat_V = 'All'
			 insert @fn_result  select * from @results where End_Customer = @endcustomer
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
				and End_Customer = @endcustomer
		else if @stat_V = 'HOLD' insert @fn_result
			  select * from @results where Status = 'HOLD' and  End_Customer = @endcustomer
		else insert @fn_result
			  select * from @results where FinalStatus = @stat_V and End_Customer = @endcustomer
	end
	if(@product_id_I = 0 and @customer_id_I <> 0  and @endcustomer <> '0')
	begin
		if @stat_V = 'All'
			 insert @fn_result  select * from @results where End_Customer = @endcustomer and Customer_ID = @customer_id_I
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
				and End_Customer = @endcustomer and Customer_ID = @customer_id_I
		else if @stat_V = 'HOLD' insert @fn_result
			  select * from @results where Status = 'HOLD' and  End_Customer = @endcustomer and Customer_ID = @customer_id_I
		else insert @fn_result
			  select * from @results where FinalStatus = @stat_V and End_Customer = @endcustomer and Customer_ID = @customer_id_I
	end
	if(@product_id_I <> 0 and @customer_id_I = 0  and @endcustomer <> '0')
	begin
		if @stat_V = 'All'
			 insert @fn_result  select * from @results where End_Customer = @endcustomer and Product_ID = @product_id_I
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
				and End_Customer = @endcustomer and Product_ID = @product_id_I
		else if @stat_V = 'HOLD' insert @fn_result
			  select * from @results where Status = 'HOLD' and  End_Customer = @endcustomer and Product_ID = @product_id_I
		else insert @fn_result
			  select * from @results where FinalStatus = @stat_V and End_Customer = @endcustomer and Product_ID = @product_id_I
	end
	if(@product_id_I <> 0 and @customer_id_I <> 0  and @endcustomer = '0')
	begin
		if @stat_V = 'All'
			 insert @fn_result  select * from @results where Customer_ID = @customer_id_I and Product_ID = @product_id_I
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
				and Customer_ID = @customer_id_I and Product_ID = @product_id_I
		else if @stat_V = 'HOLD' insert @fn_result
			  select * from @results where Status = 'HOLD' and  Customer_ID = @customer_id_I and Product_ID = @product_id_I
		else insert @fn_result
			  select * from @results where FinalStatus = @stat_V and Customer_ID = @customer_id_I and Product_ID = @product_id_I
	end
	if(@product_id_I <> 0 and @customer_id_I <> 0  and @endcustomer <> '0' )
	begin
		if @stat_V = 'All'
			 insert @fn_result  select * from @results where Customer_ID = @customer_id_I 
				and Product_ID = @product_id_I and End_Customer = @endcustomer
		else if @stat_V = 'SHIPPED' insert @fn_result  
			select * from @results where (FinalStatus = @stat_V or Status = 'PARTIALLY SHIPPED' )
				and Customer_ID = @customer_id_I and Product_ID = @product_id_I and End_Customer = @endcustomer
		else if @stat_V = 'HOLD' insert @fn_result
			  select * from @results where Status = 'HOLD' and  Customer_ID = @customer_id_I
				and Product_ID = @product_id_I and End_Customer = @endcustomer
		else insert @fn_result
			  select * from @results where FinalStatus = @stat_V and Customer_ID = @customer_id_I
				and Product_ID = @product_id_I and End_Customer = @endcustomer	
	end
return

end
